USE bank;
#存款类型
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (1,'活期','按存款日结算利息');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (2,'定期一年','存款期是1年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (3,'定期二年','存款期是2年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (4,'定期三年','存款期是3年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (5,'定活两便','无');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (6,'通知','无');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (7,'零存整取一年','存款期是1年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (8,'零存整取二年','存款期是2年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (9,'零存整取三年','存款期是3年');
INSERT INTO `deposit` (`depositnum`,`depositname`,`depositdes`) VALUES (10,'存本取息五年','按月支取利息');
SELECT * FROM `deposit`;
DELETE FROM deposit;

#客户信息
INSERT INTO `userinfo`(`name`,`id`,`numbers`,`address`)VALUES('张三','123456789012345999','01067898978','北京海淀');
INSERT INTO `userinfo`(`name`,`id`,`numbers`,`address`)VALUES('李四','321245678912345678','76544443333','云南贵州');
INSERT INTO `userinfo`(`name`,`id`,`numbers`,`address`)VALUES('王五','567891234532124670','01044443333','湖南长沙');
INSERT INTO `userinfo`(`name`,`id`,`numbers`,`address`)VALUES('丁六','567891321242345618','05243345543','广东东莞');

#银行卡信息
INSERT INTO `cardinfo`(`cardID`,`depositnum`,`cardmoney`,`money`,`customerID`,`password`,`status`)VALUES('1010357612345678',1,1000,1000,1,'123321',0);
INSERT INTO `cardinfo`(`cardID`,`depositnum`,`cardmoney`,`money`,`customerID`,`password`,`status`)VALUES('1010357612121134',2,2000,2000,2,'123456',0);
INSERT INTO `cardinfo`(`cardID`,`depositnum`,`cardmoney`,`money`,`customerID`,`password`,`status`)VALUES('1010357612121130',2,3000,3000,3,'789789',0);
INSERT INTO `cardinfo`(`cardID`,`depositnum`,`cardmoney`,`money`,`customerID`,`password`,`status`)VALUES('1010357612121004',2,4000,4000,4,'456456',0);
#alter table cardinfo drop currency;
SELECT * FROM userInfo;
SELECT * FROM cardInfo;

/*--------------交易信息表插入交易记录--------------------------*/
SELECT * FROM cardInfo;
SELECT * FROM tradeInfo;
INSERT INTO `tradeinfo`(`tradeType`,`cardID`,`tradeMoney`,`tradedes`) VALUES('支取','1010357612345678',900,'取出900元');
INSERT INTO `tradeinfo`(`tradeType`,`cardID`,`tradeMoney`,`tradedes`) VALUES('存入','1010357612121134',5000,'存入5000元');   
/*-------------更新银行卡信息表中的现有余额-------------------*/  
UPDATE `cardinfo` SET `money`=`money`-900 WHERE `cardID`='1010357612345678';
UPDATE `cardinfo` SET `money`=`money`+5000 WHERE `cardID`='1010357612121134';

#1）修改客户密码
#修改客户密码
#张三（卡号为1010 3576 1234 5678）修改银行卡密码为123321
#李四（卡号为1010 3576 1212 1134）修改银行卡密码为123456

UPDATE cardinfo SET `password` = '666666' WHERE cardid = '1010357612345678';
UPDATE cardinfo SET `password` = '888888' WHERE cardid = '1010357612121134';

#2）办理银行卡挂失
#办理银行卡挂失
#李四（卡号为1010 3576 1212 1134）因银行卡丢失，申请挂失
UPDATE cardinfo SET `status` = 1 WHERE cardid = '1010357612121134';

SELECT cardid 卡号,`password` 密码,depositname 储蓄种类,carddate 开户日期,cardmoney 开户金额,money 余额,`name` 客户姓名,`status` 是否挂失
FROM cardinfo c JOIN deposit d ON c.`depositnum` = d.`depositnum`
JOIN userinfo u ON c.`customerid` = u.`customerid`;

#3）统计银行总存入金额和总支取金额
SELECT tradetype 交易类型,SUM(trademoney) FROM tradeinfo 
GROUP BY tradetype;

#4）查询本周开户信息
SELECT u.* FROM userinfo u
JOIN cardinfo c ON u.`customerid` = c.`customerid`
WHERE WEEK(CURDATE(),1) = WEEK(c.`carddate`,1) AND YEAR(CURDATE()) = YEAR(c.`carddate`);

#5）查询本月交易金额最高的卡号
SELECT cardid FROM tradeinfo t
WHERE trademoney = 
(SELECT MAX(trademoney) FROM tradeinfo WHERE MONTH(tradedate) = MONTH(CURDATE()));

#6）查询挂失客户
SELECT `name`,numbers FROM userinfo WHERE customerid IN(SELECT customerid FROM cardinfo WHERE `status` = 1);

#7）催款提醒业务
SELECT `name`,numbers,ROUND(money,1) money FROM userinfo u
JOIN cardinfo c ON u.`customerid` = c.`customerid`
WHERE money < 200;

#创建、使用客户有好信息视图
#用例6：创建，使用客户有好信息视图
#view_userInfo：输出银行客户记录
DROP VIEW view_userinfo IF EXISTS;
CREATE VIEW view_userinfo 
AS 
SELECT customerid 客户编号,id 客户身份证号,`name` 客户姓名,numbers 客户电话,address 客户地址 FROM userinfo;
SELECT * FROM view_userinfo;

#view_cardInfo：输出银行卡记录
DROP VIEW view_cardinfo IF EXISTS;
CREATE VIEW view_cardinfo
AS 
SELECT cardid 银行卡号,`name` 客户名称,depositname 存款类型,carddate 开卡日期,`password` 密码,money 余额,`status` 是否挂失
FROM cardinfo JOIN userinfo ON cardinfo.`customerid` = userinfo.`customerid`
JOIN deposit ON deposit.`depositnum` = cardinfo.`depositnum`;
SELECT * FROM view_cardinfo;

#view_transInfo：输出银行卡的交易记录
DROP VIEW view_tradeinfo IF EXISTS;
CREATE VIEW view_tradeinfo
AS 
SELECT `tradedate` 交易日期,`tradetype` 交易类型,`tradedes` 交易简述,`trademoney` 交易金额,cardid 卡号 FROM tradeinfo;
SELECT * FROM view_tradeinfo;

#使用事务完成转账
/*-从卡号为“1010357612121134”的账户中转出2000元给卡号为“1010357612345678”的账户-*/
SHOW VARIABLES LIKE '%autocommit%';
SET autocommit = off;
BEGIN;
UPDATE cardinfo SET money = money - 2000 WHERE cardid = '1010357612121134';
INSERT INTO tradeinfo (cardid,tradetype,trademoney,tradedes)VALUES('1010357612121134','支取',2000,'账户为1010357612121134的用户转出2000元');
UPDATE cardinfo SET money = money + 2000 WHERE cardid = '1010357612345678';
INSERT INTO tradeinfo (cardid,tradetype,trademoney,tradedes)VALUES('1010357612345678','存入',2000,'账户为1010357612345678的用户收入2000元');
COMMIT;#提交事务
#roll back;回滚，撤销之前的操作
SET autocommit = ON;
SELECT * FROM cardinfo;
SELECT * FROM tradeinfo;
#查看操作的结果


